package com.oscar.ehome.utils.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.oscar.ehome.utils.util.CommonUtils;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DataBaseDAO {
    private static String logTag = DataBaseDAO.class.getSimpleName();
    /**
     * 数据库名称
     */

    public static String dataBaseName = "ehomeFile.db3";
    public CreateDataBase dbh;
    public SQLiteDatabase db;

    public DataBaseDAO(Context context) {
        if (dbh == null) {
            Log.d("DataBaseDAO", "init database");
            int version = 1;
            String sdPath = CommonUtils.getSDCardRootPath(context);
            try {

                if (StringUtils.isNotEmpty(sdPath)) {
                    String filePath = sdPath + "/iems";
                    File myFilePath = new File(filePath);
                    if (!myFilePath.exists()) {
                        myFilePath.mkdir();
                    }

                    dbh = new CreateDataBase(context, filePath + "/" + dataBaseName, version);

                } else {

                    dbh = new CreateDataBase(context, dataBaseName, version);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 查询表中的所有数据
     *
     * @param tableName 表名
     * @return 返回查询的数据，查询异常返回null
     */
    public List<Map<String, Object>> selectAll(String tableName) {

        List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
        try {
            openDBRead();
            Cursor cursor = db.rawQuery("select _id , * from " + tableName,
                    null);
            String names[] = cursor.getColumnNames();
            while (cursor.moveToNext()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < names.length; i++) {
                    map.put(cursor.getColumnName(i), cursor.getString(i));
                }
                datas.add(map);
            }
            cursor.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "查询数据库失败，错误信息 ：" + e.getLocalizedMessage());
        } finally {
            closeDB();
        }
        return datas;
    }

    /**
     * 查询数据
     *
     * @param sql  查询语句
     * @param args 参数
     * @return 返回查询的数据，查询异常返回null
     */
    public List<Map<String, Object>> select(String sql, String[] args) {
        List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
        Log.d(logTag, "开始查询数据库");
        try {
            openDBRead();
            Cursor cursor = db.rawQuery(sql, args);
            String names[] = cursor.getColumnNames();
            while (cursor.moveToNext()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < names.length; i++) {
                    map.put(cursor.getColumnName(i), cursor.getString(i));
                }
                datas.add(map);
            }
            cursor.close();
            // Log.d(logTag, datas.toString()+"===");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "查询数据库失败，错误信息 ：" + e.getLocalizedMessage());
        } finally {
            closeDB();
        }
        return datas;
    }

    /**
     * 查询数据--分页
     *
     * @param sql      查询语句
     * @param args     参数
     * @param page     页码
     * @param pageSize 每页显示条数
     * @return 返回查询数据，查询异常返回null
     */
    public List<Map<String, Object>> select(String sql, String[] args,
                                            int page, int pageSize) {
        List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
        if (page < 0) {
            page = 0;
        }
        if (pageSize > 1) {
            sql += " limit " + pageSize + " offset " + page * pageSize;
        }
        try {
            openDBRead();
            Cursor cursor = db.rawQuery(sql, args);
            String names[] = cursor.getColumnNames();
            while (cursor.moveToNext()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < names.length; i++) {
                    map.put(cursor.getColumnName(i), cursor.getString(i));
                }
                datas.add(map);
            }
            cursor.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "查询数据库失败，错误信息 ：" + e.getLocalizedMessage());
        } finally {
            closeDB();
        }
        return datas;
    }

    /**
     * 删除数据
     *
     * @param tableName 表名
     * @param clause    条件
     * @param args      参数值
     * @return 成功返回index, index 大于0；失败返回 -1
     */

    public int delete(String tableName, String clause, String[] args) {
        int reslutIndex = -1;
        try {
            openDBWrite();
            reslutIndex = db.delete(tableName, clause, args);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "删除数据库数据失败，错误信息 ：" + e.getLocalizedMessage());
        } finally {
            closeDB();
        }
        return reslutIndex;
    }

    /**
     * 删除表中所有的数据
     *
     * @param tableName 表名
     * @return 成功返回0；失败返回 -1
     */
    public int deleteAll(String tableName) {
        try {
            openDBWrite();
            db.delete(tableName, "", new String[]{});
            return 0;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "删除数据库数据失败，错误信息 ：" + e.getLocalizedMessage());
        } finally {
            closeDB();
        }
        return -1;
    }

    /**
     * 插入数据
     *
     * @param tableName 表名
     * @param data      数据
     */
    public long insert(String tableName, Map<String, Object> data) {
        Set<String> set = data.keySet();
        if (set.size() > 0) {// 数据不为空
            try {
                openDBWrite();
                ContentValues tmpValue = new ContentValues();
                for (String name : set) {
                    tmpValue.put(name, MapUtils.getString(data, name, ""));
                }
                return db.insert(tableName, null, tmpValue);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                Log.e(logTag, "插入数据库数据失败，错误信息 ：" + e.getLocalizedMessage());
            } finally {
                closeDB();
            }

        }
        return -2;

    }

    /**
     * 插入List数据
     * 注：List中每条数据的key必须一致，包括个数和顺序
     *
     * @param tableName 表名
     * @param data      数据
     */
    public long insertList(String tableName, List<Map<String, Object>> data) {
        long state = 1;
        Log.d("DatabaseDao", "insertList start");
        try {
            if (data.size() < 1) {
                return -1;
            }
            openDBWrite();
            Map<String, Object> col = data.get(0);
            Set<String> setCol = col.keySet();
            String colName = "";
            String ques = "";
            for (String name : setCol) {
                colName += (colName.equalsIgnoreCase("") ? name : " , " + name);
                ques += (ques.equalsIgnoreCase("") ? "?" : " , ?");
            }

            String sql = "INSERT INTO " + tableName + " (" + colName + ") VALUES (" + ques + ")";
            db.beginTransaction();

            SQLiteStatement stmt = db.compileStatement(sql);
            for (Map<String, Object> map : data) {
                int index = 1;
                for (String name : setCol) {// 这里和拼接sql语句用同一个set，确保字段的顺序一致
                    stmt.bindString(index++, MapUtils.getString(map, name, ""));
                }
                stmt.execute();
                stmt.clearBindings();
            }

            db.setTransactionSuccessful();
            db.endTransaction();
        } catch (Exception e) {
            state = -2;
            Log.e(logTag, "插入数据库数据失败，错误信息 ：" + e.getLocalizedMessage());
        } finally {
            Log.d("DatabaseDao", "insertList end");
            closeDB();
        }
        return state;

    }

    /**
     * 更新数据
     *
     * @param tableName 表名
     * @param data      数据
     * @param clause    约束
     * @param args      约束对应数据
     * @return 成功返回 0；失败返回 -1
     */
    public int update(String tableName, Map<String, Object> data,
                      String clause, String[] args) {
        Set<String> set = data.keySet();
        int resCode = -1;
        if (set.size() > 0) {// 数据不为空
            try {
                openDBWrite();
                ContentValues tmpValue = new ContentValues();
                for (String name : set) {
                    tmpValue.put(name, MapUtils.getString(data, name, ""));
                }
                resCode = db.update(tableName, tmpValue, clause, args);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                Log.e(logTag, "更新数据库数据失败，错误信息 ：" + e.getLocalizedMessage());
            } finally {
                closeDB();
            }
        }
        return resCode;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     */
    public int exeSQL(String sql) {
        int res = 0;
        try {
            openDBWrite();
            db.execSQL(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "Sql执行失败，错误信息 ：" + e.getLocalizedMessage());
            res = -1;
        } finally {
            closeDB();
        }
        return res;
    }

    /**
     * 执行sql语句，带占位符
     *
     * @param sql
     * @param args 占位符对应数据
     */
    public int exeSQL(String sql, Object[] args) {
        int res = 0;
        try {
            openDBWrite();
            db.execSQL(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            Log.e(logTag, "Sql执行失败，错误信息 ：" + e.getLocalizedMessage());
            res = -1;
        } finally {
            closeDB();
        }
        return res;
    }

    public void openDBWrite() {
        while (db != null && db.isOpen() && db.isDbLockedByCurrentThread()) {
            Log.d(logTag, "数据库被锁住");
        }
        db = dbh.getWritableDatabase();
    }

    public void openDBRead() {
        db = dbh.getWritableDatabase();
    }

    /**
     * 关闭数据库
     */
    public void closeDB() {
        if (db != null && db.isOpen()) {
            db.close();
        }
    }
}
